﻿Imports System.Configuration
Module Modulo
#Region "Variables"
    Public conexion As String = ConfigurationManager.ConnectionStrings("tangoConnectionString").ConnectionString
    Public cn As New SqlClient.SqlConnection(conexion)
    Public COD_ARTICU, DESCRIPCIO, COD_MEDIDA As String
    Public PRECIO As Double
    Public data_Articulos As DataTable
#End Region
   
    Public Function Conectar() As Boolean
        If cn.State <> ConnectionState.Open Then
            Try
                cn.Open()
                Return True
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Return False
            End Try
        Else
            Return True
        End If
    End Function

    Sub imprime_cotizacion(ByVal talon As Integer, ByVal cotiz As String)
        Conectar()

        Dim daDatos As New SqlClient.SqlDataAdapter ' Objeto Adaptador para leer datos de la Base de datos
        Dim cmdExec As New SqlClient.SqlCommand ' objeto comando para ejecutar sentencias sql
        Dim dtDatos As New DataTable ' datatable para recibir los datos de la base de datos
        Dim sbQuery As String ' StringBuilder para armar cadenas
        Dim form_reporte As New Form_reporte

        Try
            Conectar()
            cmdExec = cn.CreateCommand
            cmdExec.Connection = cn

            sbQuery = "SELECT GVA08.N_COTIZ,GVA08.FECHA_ALTA,GVA08.FECHA_VIG,(GVA08.IMPORTE + GVA08.IMPORTE_IV) AS TOTAL_COTIZ,GVA08.OBSERVA_1, " & _
                      "GVA14.CUIT,GVA14.RAZON_SOCI,GVA14.DOMICILIO,GVA14.LOCALIDAD,GVA14.N_IMPUESTO, GVA01.DESC_COND, GVA23.NOMBRE_VEN, " & _
                      "GVA09.CANT_TOTAL,GVA09.COD_ARTICU,GVA09.NRO_RENGL,GVA09.PRECIO,GVA09.TALONARIO,GVA09.IMPORTE," & _
                      "STA11.DESCRIPCIO,OT_cotizacion.desc_agregada " & _
                      "FROM gva08  " & _
                      "JOIN GVA14 ON GVA08.COD_CLIENT = GVA14.COD_CLIENT " & _
                      "JOIN GVA01 ON GVA08.COND_VTA = GVA01.COND_VTA " & _
                      "JOIN GVA23 ON GVA08.COD_VENDED = GVA23.COD_VENDED " & _
                      "JOIN GVA09 ON GVA08.N_COTIZ = GVA09.N_COTIZ AND GVA08.TALONARIO = GVA09.TALONARIO " & _
                      "JOIN STA11 ON GVA09.COD_ARTICU = STA11.COD_ARTICU " & _
                      "JOIN OT_cotizacion ON GVA09.TALONARIO = OT_cotizacion.TALONARIO AND GVA09.N_COTIZ = OT_cotizacion.N_COTIZ " & _
                      "AND GVA09.NRO_RENGL = OT_cotizacion.NRO_RENGL " & _
                      "WHERE GVA09.TALONARIO = " & talon & " AND GVA09.N_COTIZ =  ' " & cotiz & "'"
            cmdExec.CommandText = sbQuery.ToString



            daDatos = New SqlClient.SqlDataAdapter(cmdExec) '' ejecuto la consulta 
            daDatos.Fill(dtDatos) ''cargo en el dataset


            If dtDatos.Rows.Count > 0 Then
                Dim CrReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
                ' Asigno el reporte
                CrReport = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
                CrReport.Load(Application.StartupPath & "\Reportes\repo_cotizacion.rpt") '' imprime reporte de inicio
                CrReport.SetDataSource(dtDatos)
                form_reporte.CrystalReportViewer1.ReportSource = CrReport
                form_reporte.ShowDialog()
            Else
                MessageBox.Show("No Existen datos para el informe")
            End If
        Catch ex As Exception
            MessageBox.Show("excepcion: " & ex.Message, "Mostrando Reporte")
        End Try
    End Sub
    Sub nueva_cotizacion(ByVal talon As Integer, ByVal cotiz As String)
        Conectar()

        Dim daDatos As New SqlClient.SqlDataAdapter ' Objeto Adaptador para leer datos de la Base de datos
        Dim cmdExec As New SqlClient.SqlCommand ' objeto comando para ejecutar sentencias sql
        Dim dtDatos As New DataTable ' datatable para recibir los datos de la base de datos
        Dim sbQuery As String ' StringBuilder para armar cadenas
        Dim form_reporte As New Form_reporte

        Try
            Conectar()
            cmdExec = cn.CreateCommand
            cmdExec.Connection = cn

            sbQuery = "SELECT GVA08.N_COTIZ,GVA08.FECHA_ALTA,GVA08.FECHA_VIG,(GVA08.IMPORTE + GVA08.IMPORTE_IV) AS TOTAL_COTIZ,GVA08.OBSERVA_1, " & _
                      "GVA14.CUIT,GVA14.RAZON_SOCI,GVA14.DOMICILIO,GVA14.LOCALIDAD,GVA14.N_IMPUESTO, GVA01.DESC_COND, GVA23.NOMBRE_VEN, " & _
                      "GVA09.CANT_TOTAL,GVA09.COD_ARTICU,GVA09.NRO_RENGL,GVA09.PRECIO,GVA09.TALONARIO,GVA09.IMPORTE," & _
                      "STA11.DESCRIPCIO,OT_cotizacion.desc_agregada " & _
                      "FROM gva08  " & _
                      "JOIN GVA14 ON GVA08.COD_CLIENT = GVA14.COD_CLIENT " & _
                      "JOIN GVA01 ON GVA08.COND_VTA = GVA01.COND_VTA " & _
                      "JOIN GVA23 ON GVA08.COD_VENDED = GVA23.COD_VENDED " & _
                      "JOIN GVA09 ON GVA08.N_COTIZ = GVA09.N_COTIZ AND GVA08.TALONARIO = GVA09.TALONARIO " & _
                      "JOIN STA11 ON GVA09.COD_ARTICU = STA11.COD_ARTICU " & _
                      "JOIN OT_cotizacion ON GVA09.TALONARIO = OT_cotizacion.TALONARIO AND GVA09.N_COTIZ = OT_cotizacion.N_COTIZ " & _
                      "AND GVA09.NRO_RENGL = OT_cotizacion.NRO_RENGL " & _
                      "WHERE GVA09.TALONARIO = " & talon & " AND GVA09.N_COTIZ =  ' " & cotiz & "'"
            cmdExec.CommandText = sbQuery.ToString



            daDatos = New SqlClient.SqlDataAdapter(cmdExec) '' ejecuto la consulta 
            daDatos.Fill(dtDatos) ''cargo en el dataset


            If dtDatos.Rows.Count > 0 Then
                Dim CrReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
                ' Asigno el reporte
                CrReport = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
                CrReport.Load(Application.StartupPath & "\Reportes\repo_cotizacion.rpt") '' imprime reporte de inicio
                CrReport.SetDataSource(dtDatos)
                form_reporte.CrystalReportViewer1.ReportSource = CrReport
                form_reporte.ShowDialog()
            Else
                MessageBox.Show("No Existen datos para el informe")
            End If
        Catch ex As Exception
            MessageBox.Show("excepcion: " & ex.Message, "Mostrando Reporte")
        End Try
    End Sub



    Sub limpia_grilla(ByVal data As DataGridView)
        Dim i As Integer
        i = data.Rows.Count - 1
        Do While i >= 0
            data.Rows.RemoveAt(i)
            i = i - 1
        Loop
    End Sub
    Function limpiar_panel_text(ByVal oPanel As Panel)
        Try
            For Each oControl As Control In oPanel.Controls
                If TypeOf oControl Is TextBox Then
                    oControl.Text = ""
                ElseIf TypeOf oControl Is ComboBox Then
                    combos_sin_seleccion(oControl)
                End If
            Next
        Catch ex As Exception
        End Try

    End Function
    Function combos_sin_seleccion(ocombo As ComboBox)
        Try
            If (ocombo.ValueMember.ToString).Length > 0 Then
                ocombo.SelectedIndex = -1
            Else
                ocombo.SelectedIndex = 0
            End If
        Catch ex As Exception
        End Try

    End Function
    Function setear_grid_global(ByVal oGrid As DataGridView)
        Try
            'Dim icolum As Integer = oGrid.Columns.Count
            'For i = 0 To icolum - 1
            '    oGrid.Columns(i).Width = tamaño
            'Next
            oGrid.AllowUserToAddRows = False
            oGrid.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            oGrid.RowHeadersVisible = False
        Catch ex As Exception
        End Try

    End Function
    Public Function carga_combo_global(ByVal oCombo As ComboBox, ByVal query As String, ByVal displayM As String, ByVal valueM As String)
        Dim Cmd As SqlClient.SqlCommand
        Dim Adp As SqlClient.SqlDataAdapter
        Dim ds As New DataTable
        ds.Clear()
        Conectar()
        Try
            Cmd = New SqlClient.SqlCommand(query, cn)
            Adp = New SqlClient.SqlDataAdapter(Cmd)
            Adp.Fill(ds)
            If ds.Rows.Count > 0 Then
                oCombo.DataSource = ds
                oCombo.DisplayMember = displayM.Trim
                oCombo.ValueMember = valueM
                oCombo.DropDownStyle = ComboBoxStyle.DropDown
                oCombo.AutoCompleteSource = AutoCompleteSource.ListItems
                oCombo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
                oCombo.SelectedIndex = -1
            Else
                ds.Clear()
                oCombo.DataSource = ds
            End If
        Catch ex As Exception
            MessageBox.Show("Error al cargar " & oCombo.Name & ": " & ex.Message & "")
        End Try
        '  'desconectar()
    End Function
    Function obtengo_tabla_articulos() As Boolean
        Dim Cmd As SqlClient.SqlCommand
        Dim Adp As SqlClient.SqlDataAdapter
        Dim ds As New DataTable
        Dim sbQuery As String

        ds.Clear()
        Conectar()
        Try

            sbQuery = "select STA11.COD_ARTICU,DESCRIPCIO,COD_MEDIDA,PRECIO from sta11 " & _
                      "LEFT JOIN MEDIDA ON STA11.ID_MEDIDA_STOCK = MEDIDA.ID_MEDIDA " & _
                      "LEFT JOIN GVA17 ON STA11.COD_ARTICU = GVA17.COD_ARTICU"

            Cmd = New SqlClient.SqlCommand(sbQuery, cn)
            Adp = New SqlClient.SqlDataAdapter(Cmd)
            Adp.Fill(ds)
            data_Articulos = ds
            If data_Articulos.Rows.Count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        End Try
    End Function

End Module
